# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	return get_columns(filters), get_data(filters)
	

def get_columns(filters):
	columns = [
		{
			"label": _("单据号"),
			"fieldtype": "Link",
			"fieldname": "name",
			"options": "SRM Handboard Request",
			"width": 150,
		},
		{
			"label": _("附件"),
			"fieldtype": "Data",
			"fieldname": "attachment",
			"width": 80,
		},
		{
			"label": _("项目名称"),
			"fieldtype": "Data",
			"fieldname": "project_name",
			"width": 150,
		},
		{
			"label": _("Next Status"),
			"fieldtype": "Data",
			"fieldname": "next_status",
			"width": 120,
		},
		{
			"label": _("制单日期"),
			"fieldtype": "Date",
			"fieldname": "date",
			"width": 150,
		},
		{
			"label": _("Requirement Date"),
			"fieldtype": "Date",
			"fieldname": "requirement_date",
			"width": 150,
		},
		{
			"label": _("Frame Model"),
			"fieldtype": "Data",
			"fieldname": "frame_model",
			"width": 100,
		},
		{
			"label": _("Mold Category"),
			"fieldtype": "Data",
			"fieldname": "mold_category",
			"width": 100,
		},
		{
			"label": _("Parts Name"),
			"fieldtype": "Data",
			"fieldname": "item_name",
			"width": 100,
		},
		{
			"label": _("Product Material"),
			"fieldtype": "Data",
			"fieldname": "product_material",
			"width": 100,
		},
		{
			"label": _("Qty"),
			"fieldtype": "Int",
			"fieldname": "qty",
			"width": 100,
		},
		{
			"label": _("Description"),
			"fieldtype": "Data",
			"fieldname": "description",
			"width": 100,
		},
		{
			"label": _("Amount Of Money"),
			"fieldtype": "Data",
			"fieldname": "amount_of_money",
			"width": 100,
		},
		{
			"label": _("Department And Line"),
			"fieldtype": "Data",
			"fieldname": "department_and_line",
			"width": 120,
		},
		{
			"label": _("申请人"),
			"fieldtype": "Data",
			"fieldname": "applicant_username",
			"width": 70,
		},
	]

	return columns


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	priority, next_status, creation_from, creation_to = filters.get('priority'), filters.get('next_status'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if next_status:
		conditions += f" AND s1.next_status = '{next_status}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""select s1.name, s1.date, s1.project_name, 
			s1.department_and_line, s1.frame_model, 
			s1.next_status, 
			s1.mold_category, s1.requirement_date,
			s1.applicant_username, IFNULL(s1.amount_of_money,'') as amount_of_money,
			s1.creation,
			s1.frame_model,
			s1.mold_category,
			s1.requirement_date,
			s1.amount_of_money,
			s2.item_name, s2.product_material, s2.qty, s2.description,
			concat('<a href=', IFNULL(s1.attachment, ''), '>', IFNULL(substring(s1.attachment, 10, 4), ''), '</a>') as attachment
			from `tabSRM Handboard Request` s1
			join `tabSRM Handboard Request Sub` s2 on s1.name = s2.parent
			WHERE s1.creation >= '{creation_from}' and s1.creation <= '{creation_to}' {conditions}
			ORDER BY s1.creation DESC;
		""", as_dict=True
	)

	return dt

#  when 'Save' then '保存'
# 					when 'Hand In' then '提交'
# 					when '硬件主管审核' then '硬件主管审核'
# 					when '部门经理审核' then '部门经理审核'
# 					when '中心副总审核' then '中心副总审核'
# 					when '产品线审核' then '产品线审核'
# 					when 'Abolish' then '废止'
# 					when 'Finish' then '完成' 
# 					when 'Cancelled' then '取消' end as workflow_state